#include <dlfcn.h>
#include <limits.h>
#include "executesql.h"
#include "ctrl.h"
#include "common.h"

extern sqlite3 *g_sqlite3_db;
extern ob_context g_mqtt_ob;

// 从查询结果中读取字符串并转为 unsigned long long
unsigned long long read_ull_from_db(sqlite3_stmt *stmt, int column_index)
{
    const char *text = (const char *)sqlite3_column_text(stmt, column_index);
    if (text == NULL)
    {
        // 处理空值或错误
        fprintf(stderr, "Database field is NULL\n");
        return -1; // 或自定义错误码
    }

    char *endptr;
    errno = 0; // 清除错误标志
    unsigned long long value = strtoull(text, &endptr, 10);

    // 错误检查
    if ((errno == ERANGE && value == ULLONG_MAX) || (errno != 0 && value == 0))
    {
        fprintf(stderr, "数值溢出或转换错误\n");
        return -2;
    }
    if (endptr == text)
    {
        fprintf(stderr, "无效数字字符串: %s\n", text);
        return -3;
    }
    if (*endptr != '\0')
    {
        fprintf(stderr, "含非法后缀字符: %s\n", endptr);
        return -4;
    }

    return value;
}

/// @brief 插入文件监控数据包
/// @param param
/// @return
bool SqlINSERTIntoDirectoryMonitoring(char *param)
{
    bool ret = false;

    sqlite3_stmt *stmt = NULL;
    const char *sql = "INSERT INTO directory_monitoring (path) VALUES (?)";

    int rc = sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("SqlINSERTIntoDirectoryMonitoring: %s error:%s\n", sql, sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 绑定参数的值
    sqlite3_bind_text(stmt, 1, param, -1, SQLITE_STATIC);

    // 执行 SQL 语句
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE)
    {
        dzlog_error("SqlINSERTIntoDirectoryMonitoring error: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }
    // 重置预处理语句以便可重复使用
    sqlite3_reset(stmt);
    ret = true;
error:
    if(stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }

    return ret;
}

////@brief 更新文件监控数据包
////@param param
////@return
// bool SqlDeleteAppInfo(char* param)
//{
//     sqlite3_stmt* stmt;
//
//     const char* sql = "DELETE  FROM APP_LIST WHERE appid=?";
//
//     int rc = sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL);
//     if (rc != SQLITE_OK)
//     {
//         dzlog_error("SqlDeleteAppInfo SQL: %s error:%s\n", sql, sqlite3_errmsg(g_sqlite3_db));
//         return false;
//     }
//
//     // 绑定参数的值
//
//     sqlite3_bind_text(stmt, 1, param, -1, SQLITE_STATIC);
//
//     // 执行 SQL 语句
//     rc = sqlite3_step(stmt);
//     if (rc != SQLITE_DONE)
//     {
//         dzlog_error("SqlDeleteAppInfo 执行 SQL 语句出错: %s\n", sqlite3_errmsg(g_sqlite3_db));
//         return false;
//     }
//
//     // 重置预处理语句以便可重复使用
//     sqlite3_reset(stmt);
//
//     // 释放资源
//     sqlite3_finalize(stmt);
//     return true;
// }

//@brief 删除文件监控数据包
//@param param
//@return
bool SqlDeleteDirectoryMonitoring()
{
    bool ret = false;

    sqlite3_stmt *stmt = NULL;
    const char *sql = "DELETE FROM directory_monitoring";

    int rc = sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("SqlDeleteDirectoryMonitoring SQL: %s error:%s\n", sql, sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 绑定参数的值
    // sqlite3_bind_text(stmt, 1, param, -1, SQLITE_STATIC);

    // 执行 SQL 语句
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE)
    {
        dzlog_error("SqlDeleteDirectoryMonitoring 执行 SQL 语句出错: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 重置预处理语句以便可重复使用
    sqlite3_reset(stmt);
    ret = true;
error:
    if (stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }

    return ret;
}

/// @brief 查询appid
/// @param appid
/// @param appname
/// @return
bool SqlSearchpara(char *remsg)
{
    bool ret = false;

    sqlite3_stmt *stmt = NULL;
    const char *selectQuery = "SELECT path FROM directory_monitoring";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    if (rc != SQLITE_OK)
    {
        dzlog_error("无法绑定参数: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }
    // 执行查询
    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        const char *data = (const char *)sqlite3_column_text(stmt, 0);
        if (data != NULL && strlen(data) != 0 && *data != '\0')
        {
            strcpy(remsg, data);
        }
    }
    else if (rc == SQLITE_DONE)
    {
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }
    ret = true;
error:
    if(stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }

    return ret;
}

/// @brief 插入网络策略表
/// @param param
/// @return
int SqlINSERTIntoNetworkPolicy(PIOTA_NETWORKLIST_PARAM_V1 param)
{
    int ret = -1;

    sqlite3_stmt *stmt = NULL;
    const char *sql;
    int rc = -1;
    if (param->srcIp != NULL && strlen(param->srcIp) != 0)
    {
        if (param->dstIp != NULL && strlen(param->dstIp) != 0)
        {
            sql = "INSERT INTO network_policy (policyId,permissionStatus,srcIp,srcPort,dstIp,dstPort,protocol) VALUES (?,?,?,?,?,?,?)";
            rc = sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL);
            if (rc != SQLITE_OK)
            {
                dzlog_error("SqlINSERTIntoNetworkPolicy[%s] error[%s]\n", sql, sqlite3_errmsg(g_sqlite3_db));
                goto error;
            }
            // 绑定参数的值
            sqlite3_bind_text(stmt, 1, param->policyId, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 2, param->permissionStatus, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 3, param->srcIp, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 4, param->srcPort, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 5, param->dstIp, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 6, param->dstPort, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 7, param->protocol, -1, SQLITE_STATIC);
        }
        else
        {
            sql = "INSERT INTO network_policy (policyId,permissionStatus,srcIp,srcPort,protocol) VALUES (?,?,?,?,?)";
            rc = sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL);
            if (rc != SQLITE_OK)
            {
                dzlog_error("SqlINSERTIntoNetworkPolicy[%s] error[%s]\n", sql, sqlite3_errmsg(g_sqlite3_db));
                goto error;
            }

            // 绑定参数的值
            sqlite3_bind_text(stmt, 1, param->policyId, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 2, param->permissionStatus, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 3, param->srcIp, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 4, param->srcPort, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 5, param->protocol, -1, SQLITE_STATIC);
        } 
    }
    else
    {
        if (param->srcPort != NULL && strlen(param->srcPort) != 0)
        {
            sql = "INSERT INTO network_policy (policyId,permissionStatus,srcPort,protocol) VALUES (?,?,?,?)";
            rc = sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL);
            if (rc != SQLITE_OK)
            {
                dzlog_error("SqlINSERTIntoNetworkPolicy[%s] error[%s]\n", sql, sqlite3_errmsg(g_sqlite3_db));
                goto error;
            }
            // 绑定参数的值
            sqlite3_bind_text(stmt, 1, param->policyId, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 2, param->permissionStatus, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 3, param->srcPort, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 4, param->protocol, -1, SQLITE_STATIC);
        }
        else
        {
            sql = "INSERT INTO network_policy (policyId,permissionStatus,protocol) VALUES (?,?,?)";
            rc = sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL);
            if (rc != SQLITE_OK)
            {
                dzlog_error("SqlINSERTIntoNetworkPolicy[%s] error[%s]\n", sql, sqlite3_errmsg(g_sqlite3_db));
                goto error;
            }
            // 绑定参数的值
            sqlite3_bind_text(stmt, 1, param->policyId, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 2, param->permissionStatus, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 3, param->protocol, -1, SQLITE_STATIC);
        }
    }

    // 执行 SQL 语句
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE)
    {
        dzlog_error("SqlINSERTIntoDirectoryMonitoring error[%s]\n", sqlite3_errmsg(g_sqlite3_db));
        ret = -2;
        goto error;
    }
    // 重置预处理语句以便可重复使用
    sqlite3_reset(stmt);
    ret = 0;
error:
    if(stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }

    return ret;
}

//@brief 删除网络策略记录
//@param param
//@return
int SqlDeleteNetworkPolicy(char *id)
{
    int ret = -1;
    sqlite3_stmt *stmt = NULL;

    const char *sql = "DELETE FROM network_policy WHERE policyId = ?";

    int rc = sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("SqlDeleteNetworkPolicy SQL: %s error:%s\n", sql, sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 绑定参数的值
    sqlite3_bind_text(stmt, 1, id, -1, SQLITE_STATIC);

    // 执行 SQL 语句
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE)
    {
        dzlog_error("SqlDeleteNetworkPolicy 执行 SQL 语句出错: %s\n", sqlite3_errmsg(g_sqlite3_db));
        ret = -2;
        goto error;
    }

    // 重置预处理语句以便可重复使用
    sqlite3_reset(stmt);
    ret = 0;
error:
    if(stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

//@brief 根据策略id查询网络策略记录
//@param param
//@return
int SqlSearNetworkPolicyById(char *id, PIOTA_NETWORKLIST_PARAM_V1 param)
{
    int ret = -1;

    sqlite3_stmt *stmt = NULL;
    const char *selectQuery = "SELECT policyId,permissionStatus,srcIp,srcPort,protocol FROM network_policy WHERE policyId = ?";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 绑定参数的值
    sqlite3_bind_text(stmt, 1, id, -1, SQLITE_STATIC);

    if (rc != SQLITE_OK)
    {
        dzlog_error("无法绑定参数: %s\n", sqlite3_errmsg(g_sqlite3_db));
        ret = -2;
        goto error;
    }
    // 执行查询
    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        const char *data1 = (const char *)sqlite3_column_text(stmt, 0);
        const char *data4 = (const char *)sqlite3_column_text(stmt, 3);
        if (data1 != NULL && strlen(data1) != 0 && *data1 != '\0')
        {
            strcpy(param->policyId, data1);
            strcpy(param->permissionStatus, sqlite3_column_text(stmt, 1));
            strcpy(param->srcIp, sqlite3_column_text(stmt, 2));
            strcpy(param->protocol, sqlite3_column_text(stmt, 4));
        }
        if (data4 != NULL && strlen(data4) != 0 && *data4 != '\0')
        {
            strcpy(param->srcPort, data4);
        }
    }
    else if (rc == SQLITE_DONE)
    {
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
    }
    ret = 0;
    // 释放资源
error:
    if(stmt)
    {
        sqlite3_finalize(stmt);
    }

    return ret;
}

//@brief 查询网络策略记录ALL
//@param param
//@return
int SqlSearNetworkPolicy(PIOTA_NETWORKLIST_PARAM_V1 param)
{
    int ret = -1;

    sqlite3_stmt *stmt = NULL;
    int count = 0;
    const char *selectQuery = "SELECT policyId,permissionStatus,srcIp,srcPort,protocol,dstIp,dstPort FROM network_policy";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 绑定参数的值
    // sqlite3_bind_text(stmt, 1, id, -1, SQLITE_STATIC);
    // if (rc != SQLITE_OK)
    //{
    //    dzlog_error("无法绑定参数: %s\n", sqlite3_errmsg(g_sqlite3_db));
    //    return -2;
    //}

    // 执行查询
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        const char *data1 = (const char *)sqlite3_column_text(stmt, 0);
        const char *data4 = (const char *)sqlite3_column_text(stmt, 3);
        if (data1 != NULL && strlen(data1) != 0)
        {
            strcpy(param->policyId, data1);
            strcpy(param->permissionStatus, sqlite3_column_text(stmt, 1));
            strcpy(param->srcIp, sqlite3_column_text(stmt, 2));
            strcpy(param->protocol, sqlite3_column_text(stmt, 4));
            const char* data5 = (const char*)sqlite3_column_text(stmt, 5);

            if (data5 != NULL && strlen(data5) != 0)
            {
                strcpy(param->dstIp, sqlite3_column_text(stmt, 5));
            }
            const char* data6 = (const char*)sqlite3_column_text(stmt, 6);
            if (data6 != NULL && strlen(data6) != 0)
            {
                strcpy(param->dstPort, sqlite3_column_text(stmt, 6));
            }
        }
        if (data4 != NULL && strlen(data4) != 0)
        {
            strcpy(param->srcPort, data4);
        }
        strcpy(param->operation, "DELETE");
        count++;
        param++;
    }
    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
    }
    else if (rc == SQLITE_DONE)
    {
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }
    ret = count;
error:
    if(stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

//@brief 查询网络策略记录条数
//@param param
//@return
int SqlSearNetworkPolicyCOUNT()
{
    int ret = -1;
    sqlite3_stmt *stmt = NULL;
    const char *selectQuery = "SELECT COUNT(*) FROM network_policy;";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 执行查询

    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        ret = sqlite3_column_int(stmt, 0);
    }
    else if (rc == SQLITE_DONE)
    {
        ret = 0;
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
        ret = 0;
    }
error:
    if(stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

//@brief 查询是否有相同的网络策略记录
//@param param
//@return
int SqlSearNetworkPolicyCOUNT_V1(PIOTA_NETWORKLIST_PARAM_V1 param)
{
    int ret = -1;
    sqlite3_stmt* stmt;
    //const char* selectQuery = "SELECT COUNT(*) FROM network_policy WHERE permissionStatus = ? AND srcIp = ? AND srcPort = ? AND dstIp = ? AND dstPort = ? AND protocol = ?";
    char selectQuery[512] = "SELECT COUNT(*) FROM network_policy WHERE 1 ";
    
    int parameter[6] = { 0 };
    bool ispermissionStatus = false;
    bool issrcIp = false;
    bool issrcPort = false;
    bool isdstIp = false;
    bool isdstPort = false;
    bool isprotocol = false;


    if (param->permissionStatus != NULL && strlen(param->permissionStatus) != 0)
    {
        strcat(selectQuery, "AND permissionStatus = ? ");
        ispermissionStatus = true;
        parameter[0] = 1;
    }
    if (param->srcIp != NULL && strlen(param->srcIp) != 0)
    {
        strcat(selectQuery, "AND srcIp = ? ");
        issrcIp = true;
        parameter[1] = 1;
    }
    if (param->srcPort != NULL && strlen(param->srcPort) != 0 && atoi(param->srcPort)>0)
    {
        strcat(selectQuery, "AND srcPort = ? ");
        issrcPort = true;
        parameter[2] = 1;
    }
    if (param->dstIp != NULL && strlen(param->dstIp) != 0)
    {
        strcat(selectQuery, "AND dstIp = ? ");
        isdstIp = true;
        parameter[3] = 1;
    }
    if (param->dstPort != NULL && strlen(param->dstPort) != 0 && atoi(param->dstPort) > 0)
    {
        strcat(selectQuery, "AND dstPort = ? ");
        isdstPort = true;
        parameter[4] = 1;
    }
    if (param->protocol != NULL && strlen(param->protocol) != 0)
    {
        strcat(selectQuery, "AND protocol = ?");
        isprotocol = true;
        parameter[5] = 1;
    }

    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    int i = 0;
    int j = 1;
    for (i = 0; i < 6; i++)
    {
        if (parameter[i] == 1)
        {
            switch (i) 
            {
                case 0:
                    sqlite3_bind_text(stmt, j, param->permissionStatus, -1, SQLITE_STATIC);
                    break;
                case 1:
                    sqlite3_bind_text(stmt, j, param->srcIp, -1, SQLITE_STATIC);
                    break;
                case 2:
                    sqlite3_bind_text(stmt, j, param->srcPort, -1, SQLITE_STATIC);
                    break;
                case 3:
                    sqlite3_bind_text(stmt, j, param->dstIp, -1, SQLITE_STATIC);
                    break;
                case 4:
                    sqlite3_bind_text(stmt, j, param->dstPort, -1, SQLITE_STATIC);
                    break;
                case 5:
                    sqlite3_bind_int(stmt, j, param->protocol);
                    break;
            }
            j++;
        }
    }

    // 执行查询
    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        ret = sqlite3_column_int(stmt, 0);
    }
    else if (rc == SQLITE_DONE)
    {
        ret = 0;
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
        ret = 0;
    }
error:
    if (stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

int SqlSearNetworkPolicyCOUNT_V2(PIOTA_NETWORKLIST_PARAM_V2 param)
{
    int ret = -1;
    sqlite3_stmt* stmt;
    char selectQuery[512] = "SELECT COUNT(*) FROM network_firewall WHERE 1 ";
    int parameter[9] = { 0 };
    bool ispermissionStatus = false;
    bool issrcIp            = false;
    bool issrcPort          = false;
    bool isdstIp            = false;
    bool isdstPort          = false;
    bool isprotocol         = false;
    bool ischainDirection   = false;
    bool isstate            = false;
    bool isruleOrigin       = false;

    if (param->permissionStatus != NULL && strlen(param->permissionStatus) != 0)
    {
        strcat(selectQuery, "AND permissionStatus = ? ");
        ispermissionStatus = true;
        parameter[0] = 1;
    }
    if (param->srcIp != NULL && strlen(param->srcIp) != 0)
    {
        strcat(selectQuery, "AND srcIp = ? ");
        issrcIp = true;
        parameter[1] = 1;
    }
    if (param->srcPort != NULL && strlen(param->srcPort) != 0)
    {
        strcat(selectQuery, "AND srcPort = ? ");
        issrcPort = true;
        parameter[2] = 1;
    }
    if (param->dstIp != NULL && strlen(param->dstIp) != 0)
    {
        strcat(selectQuery, "AND dstIp = ? ");
        isdstIp = true;
        parameter[3] = 1;
    }
    if (param->dstPort != NULL && strlen(param->dstPort) != 0)
    {
        strcat(selectQuery, "AND dstPort = ? ");
        isdstPort = true;
        parameter[4] = 1;
    }
    if (param->protocol != NULL && strlen(param->protocol) != 0)
    {
        strcat(selectQuery, "AND protocol = ? ");
        isprotocol = true;
        parameter[5] = 1;
    }
    if (param->chainDirection != NULL && strlen(param->chainDirection) != 0)
    {
        strcat(selectQuery, "AND chainDirection = ? ");
        ischainDirection = true;
        parameter[6] = 1;
    }
    if (param->state != NULL && strlen(param->state) != 0)
    {
        strcat(selectQuery, "AND state = ? ");
        isstate = true;
        parameter[7] = 1;
    }
    dzlog_debug("SqlSearNetworkPolicyCOUNT_V2 SQL[%s]", selectQuery);
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    int i = 0;
    int j = 1;
    for (i = 0; i < 9; i++)
    {
        if (parameter[i] == 1)
        {
            switch (i)
            {
                case 0:
                    sqlite3_bind_text(stmt, j, param->permissionStatus, -1, SQLITE_STATIC);
                    break;
                case 1:
                    sqlite3_bind_text(stmt, j, param->srcIp, -1, SQLITE_STATIC);
                    break;
                case 2:
                    sqlite3_bind_text(stmt, j, param->srcPort, -1, SQLITE_STATIC);
                    break;
                case 3:
                    sqlite3_bind_text(stmt, j, param->dstIp, -1, SQLITE_STATIC);
                    break;
                case 4:
                    sqlite3_bind_text(stmt, j, param->dstPort, -1, SQLITE_STATIC);
                    break;
                case 5:
                    sqlite3_bind_text(stmt, j, param->protocol, -1, SQLITE_STATIC);
                    break;
                case 6:
                    sqlite3_bind_text(stmt, j, param->chainDirection, -1, SQLITE_STATIC);
                    break;
                case 7:
                    sqlite3_bind_text(stmt, j, param->state, -1, SQLITE_STATIC);
                    break;
                case 8:
                    sqlite3_bind_int(stmt, j, param->ruleOrigin);
                    break;
            }
            j++;
        }
    }

    // 执行查询
    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        ret = sqlite3_column_int(stmt, 0);
    }
    else if (rc == SQLITE_DONE)
    {
        ret = 0;
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
        ret = 0;
    }
error:
    if (stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

//@brief 查询新网络策略记录ALL
//@param param
//@return
int SqlSearNetworkPolicy_V2(PIOTA_NETWORKLIST_PARAM_V2 param)
{
    int ret = -1;

    sqlite3_stmt* stmt = NULL;
    int count = 0;                                                                                                              
    const char* selectQuery = "SELECT policyId,chainDirection,permissionStatus,protocol,srcIp,srcPort,dstIp,dstPort,state,ruleOrigin,prio FROM network_firewall";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 执行查询
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        const char* data0 = (const char*)sqlite3_column_text(stmt, 0);
        if (data0 != NULL && strlen(data0) != 0)
        {
            strcpy(param->policyId, data0);
            strcpy(param->chainDirection, sqlite3_column_text(stmt, 1));
            strcpy(param->permissionStatus, sqlite3_column_text(stmt, 2));
            strcpy(param->protocol, sqlite3_column_text(stmt, 3));
            param->ruleOrigin = sqlite3_column_int(stmt, 9);
            param->prio = sqlite3_column_int(stmt, 10);
 
            const char* data1 = (const char*)sqlite3_column_text(stmt, 4);
            if (data1 != NULL && strlen(data1) != 0)
            {
                strcpy(param->srcIp, data1);
            }

            const char* data2 = (const char*)sqlite3_column_text(stmt, 6);
            if (data2 != NULL && strlen(data2) != 0)
            {
                strcpy(param->dstIp, data2);
            }
            
            const char* data3 = (const char*)sqlite3_column_text(stmt, 8);
            if (data3 != NULL && strlen(data3) != 0)
            {
                strcpy(param->state, data3);
            }

            const char* data4 = (const char*)sqlite3_column_text(stmt, 5);
            if (data4 != NULL && strlen(data4) != 0)
            {
                strcpy(param->srcPort, data4);
            }

            const char* data5 = (const char*)sqlite3_column_text(stmt, 7);
            if (data5 != NULL && strlen(data5) != 0)
            {
                strcpy(param->dstPort, data5);
            }
        }
        count++;
        param++;
    }
    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
    }
    else if (rc == SQLITE_DONE)
    {
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }
    ret = count;
error:
    if (stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

bool SqlSearNetworkPolicyByAll(PIOTA_NETWORKLIST_PARAM_V1 input)
{
    bool ret = false;
    sqlite3_stmt *stmt = NULL;

    const char *selectQuery = "SELECT policyId FROM network_policy WHERE permissionStatus = ? AND srcIp = ? AND srcPort = ? AND protocol = ?";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 绑定参数的值
    sqlite3_bind_text(stmt, 1, input->permissionStatus, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, input->srcIp, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 3, input->srcPort, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 4, input->protocol, -1, SQLITE_STATIC);
    // 执行查询

    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        const char *data1 = (const char *)sqlite3_column_text(stmt, 0);
        if (data1 != NULL && strlen(data1) != 0 && *data1 != '\0')
        {
            ret = true;
        }
    }
    else if (rc == SQLITE_DONE)
    {
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
    }
error:
    if(stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

bool SqlSearNetworkPolicyWithOutPort(PIOTA_NETWORKLIST_PARAM_V1 input)
{
    bool ret = false;
    sqlite3_stmt *stmt = NULL;
    const char *selectQuery = "SELECT policyId FROM network_policy WHERE permissionStatus = ? AND srcIp = ? AND protocol = ?";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 绑定参数的值
    sqlite3_bind_text(stmt, 1, input->permissionStatus, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, input->srcIp, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 3, input->protocol, -1, SQLITE_STATIC);
    // 执行查询

    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        const char *data1 = (const char *)sqlite3_column_text(stmt, 0);
        if (data1 != NULL && strlen(data1) != 0 && *data1 != '\0')
        {
            ret = true;
        }
    }
    else if (rc == SQLITE_DONE)
    {
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
    }
error:
    if(stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

/// @brief 查询网络流量统计表条数（网卡数）
/// @param param
/// @return
int SqlSearNetworkTrafficCOUNT()
{
    int ret = -1;
    sqlite3_stmt *stmt = NULL;
    const char *selectQuery = "SELECT COUNT(*) FROM network_traffic where date_ym = ?;";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    char date_ym[32] = {0};
    togettime_ym(date_ym);
    // 绑定参数的值
    sqlite3_bind_text(stmt, 1, date_ym, -1, SQLITE_STATIC);

    // 执行查询
    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        ret = sqlite3_column_int(stmt, 0);
    }
    else if (rc == SQLITE_DONE)
    {
        ret = 0;
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
        ret = 0;
    }
    
error:
    if(stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

//@brief 查询网络流量统计表全表
//@param param
//@return
int SqlSearNetworkTraffic(PNetInterface param)
{
    int ret = -1;
    sqlite3_stmt *stmt = NULL;
    // const char* selectQuery = "SELECT port_name,rx_total,tx_total,deltarx,deltatx FROM network_traffic";
    const char *selectQuery = "SELECT port_name,rx_total,tx_total,tx_prev,rx_prev FROM network_traffic where date_ym = ?";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    char date_ym[32] = {0};
    togettime_ym(date_ym);
    // 绑定参数的值
    sqlite3_bind_text(stmt, 1, date_ym, -1, SQLITE_STATIC);

    // 执行查询
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        const char *data0 = (const char *)sqlite3_column_text(stmt, 0);
        unsigned long long data1 = read_ull_from_db(stmt, 1);
        unsigned long long data2 = read_ull_from_db(stmt, 2);
        unsigned long long data3 = read_ull_from_db(stmt, 3);
        unsigned long long data4 = read_ull_from_db(stmt, 4);
        if (data0 != NULL && strlen(data0) != 0 && *data0 != '\0')
        {
            strcpy(param->name, data0);
        }
        param->rx_total = data1;
        param->tx_total = data2;
        param->tx_prev = data3;
        param->rx_prev = data4;

        param++;
    }

    if (rc == SQLITE_DONE)
    {
        // 查询结束
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
    }
    ret = 0;
error:
    if(stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

/// @brief 插入网络流量统计表
/// @param param
/// @return
int SqlINSERTIntoNetworkTraffic(PNetInterface param, int count)
{
    int ret = -1;

    // 参数校验
    if (!param || count <= 0)
    {
        goto error;
    }

    sqlite3_stmt *stmt = NULL;
    const char *sql = "INSERT INTO network_traffic (port_name, date_ym, rx_total, tx_total, tx_prev, rx_prev) "
                      "VALUES (?,?,?,?,?,?) "
                      "ON CONFLICT(port_name, date_ym) DO UPDATE SET "
                      "date_ym = excluded.date_ym,   "
                      "rx_total = excluded.rx_total,   "
                      "tx_total = excluded.tx_total,   "
                      "tx_prev = excluded.tx_prev,   "
                      "rx_prev = excluded.rx_prev;"; // 冲突时更新

    // 开始事务
    if (sqlite3_exec(g_sqlite3_db, "BEGIN IMMEDIATE", NULL, NULL, NULL) != SQLITE_OK)
    {
        fprintf(stderr, "开启事务失败: %s\n", sqlite3_errmsg(g_sqlite3_db));
        dzlog_error("开启事务失败: %s", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 准备预处理语句
    if (sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL) != SQLITE_OK)
    {
        fprintf(stderr, "准备语句失败: %s\n", sqlite3_errmsg(g_sqlite3_db));
        dzlog_error("准备语句失败: %s", sqlite3_errmsg(g_sqlite3_db));
        sqlite3_exec(g_sqlite3_db, "ROLLBACK", NULL, NULL, NULL);
        goto error;
    }

    // 批量操作
    char date_ym[32] = {0};
    togettime_ym(date_ym);
    int success_count = 0;
    char text_rx[21], text_tx[21], tx_prev[21], rx_prev[21]; // 独立缓冲区
    int i = 0;
    for (i = 0; i < count; i++, param++)
    {
        // 绑定参数
        sqlite3_bind_text(stmt, 1, param->name, -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 2, date_ym, -1, SQLITE_TRANSIENT);
        snprintf(text_rx, sizeof(text_rx), "%llu", param->rx_total);
        sqlite3_bind_text(stmt, 3, text_rx, -1, SQLITE_TRANSIENT);
        snprintf(text_tx, sizeof(text_tx), "%llu", param->tx_total);
        sqlite3_bind_text(stmt, 4, text_tx, -1, SQLITE_TRANSIENT);
        snprintf(tx_prev, sizeof(tx_prev), "%llu", param->tx_prev);
        sqlite3_bind_text(stmt, 5, tx_prev, -1, SQLITE_TRANSIENT);
        snprintf(rx_prev, sizeof(rx_prev), "%llu", param->rx_prev);
        sqlite3_bind_text(stmt, 6, rx_prev, -1, SQLITE_TRANSIENT);
        // 执行操作
        int rc = sqlite3_step(stmt);
        if (rc != SQLITE_DONE)
        {
            fprintf(stderr, "操作失败[%d]: %s\n", i, sqlite3_errmsg(g_sqlite3_db));
            dzlog_error("操作失败[%d]: %s\n", i, sqlite3_errmsg(g_sqlite3_db));
            continue; // 记录失败但继续执行
        }
        success_count++;
        sqlite3_reset(stmt);
    }

    if (success_count > 0)
    {
        sqlite3_exec(g_sqlite3_db, "COMMIT", NULL, NULL, NULL);
    }
    else
    {
        sqlite3_exec(g_sqlite3_db, "ROLLBACK", NULL, NULL, NULL);
    }
    ret = success_count;
error:
    if(stmt)
    {
        // 清理资源
        sqlite3_finalize(stmt);
    }
    return ret; // 返回成功插入/更新的记录数
}

/// @brief 插入密码策略表
/// @param param
/// @return
int SqlINSERTIntoPasswordPolicy(password_policy_t param)
{
    dzlog_info("SqlINSERTIntoPasswordPolicy start... 1");

    int ret = -1;
    sqlite3_stmt* stmt = NULL;
    const char* sql = "INSERT INTO password_policy (sn,minlen, minclass, lcredit, ucredit, dcredit, ocredit,is_open,edit_prof) "
        "VALUES (?,?,?,?,?,?,?,?,?) "
        "ON CONFLICT(sn) DO UPDATE SET "
        "minlen = excluded.minlen,   "
        "minclass = excluded.minclass,   "
        "lcredit = excluded.lcredit,   "
        "ucredit = excluded.ucredit,   "
        "dcredit = excluded.dcredit,   "
        "ocredit = excluded.ocredit,   "
        "is_open = excluded.is_open,   "
        "edit_prof = excluded.edit_prof;"; // 冲突时更新


    dzlog_info("SqlINSERTIntoPasswordPolicy start... 2");
    // 开始事务
    if (sqlite3_exec(g_sqlite3_db, "BEGIN IMMEDIATE", NULL, NULL, NULL) != SQLITE_OK)
    {
        fprintf(stderr, "开启事务失败: %s\n", sqlite3_errmsg(g_sqlite3_db));
        dzlog_info("SqlINSERTIntoPasswordPolicy start... 3");
        goto error;
    }

    // 准备预处理语句
    if (sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL) != SQLITE_OK)
    {
        fprintf(stderr, "准备语句失败: %s\n", sqlite3_errmsg(g_sqlite3_db));
        sqlite3_exec(g_sqlite3_db, "ROLLBACK", NULL, NULL, NULL);
        dzlog_info("SqlINSERTIntoPasswordPolicy start... 4");
        goto error;
    }
    dzlog_info("SqlINSERTIntoPasswordPolicy start... 5");
    int success_count = 0;
    // 绑定参数
    sqlite3_bind_text(stmt, 1, g_mqtt_ob.clientInfo.device_id, -1, SQLITE_STATIC);
    dzlog_info("SqlINSERTIntoPasswordPolicy start... 5 1");
    sqlite3_bind_int(stmt, 2, param.minlen);
    dzlog_info("SqlINSERTIntoPasswordPolicy start... 5 2");
    sqlite3_bind_int(stmt, 3, param.minclass);
    dzlog_info("SqlINSERTIntoPasswordPolicy start... 5 3");
    sqlite3_bind_int(stmt, 4, param.lcredit);
    dzlog_info("SqlINSERTIntoPasswordPolicy start... 5 4");
    sqlite3_bind_int(stmt, 5, param.ucredit);
    dzlog_info("SqlINSERTIntoPasswordPolicy start... 5 5");
    sqlite3_bind_int(stmt, 6, param.dcredit);
    dzlog_info("SqlINSERTIntoPasswordPolicy start... 5 6");
    sqlite3_bind_int(stmt, 7, param.ocredit);
    dzlog_info("SqlINSERTIntoPasswordPolicy start... 5 7");
    sqlite3_bind_int(stmt, 8, param.is_open);
    dzlog_info("SqlINSERTIntoPasswordPolicy start... 5 8");
    sqlite3_bind_int(stmt, 9, param.edit_prof);
    dzlog_info("SqlINSERTIntoPasswordPolicy start... 6");
    // 执行操作
    int rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE)
    {
        fprintf(stderr, "SqlINSERTIntoPasswordPolicy error: %s\n", sqlite3_errmsg(g_sqlite3_db));
    }
    else
    {
        success_count++;
        sqlite3_reset(stmt);
    }
    dzlog_info("SqlINSERTIntoPasswordPolicy start... 7");

    if (success_count > 0)
    {
        sqlite3_exec(g_sqlite3_db, "COMMIT", NULL, NULL, NULL);
    }
    else
    {
        sqlite3_exec(g_sqlite3_db, "ROLLBACK", NULL, NULL, NULL);
    }
    ret = success_count;
error:
    if(stmt)
    {
        // 清理资源
        sqlite3_finalize(stmt);
    }
    dzlog_info("SqlINSERTIntoPasswordPolicy start... 8");
    return ret; // 返回成功插入/更新的记录数
}

/// @brief 查询密码策略表
/// @param param
/// @return
int SqlQueryPasswordPolicy(password_policy_t* param)
{
    int ret = -1;

    sqlite3_stmt* stmt;
    const char* selectQuery = "SELECT minlen, minclass, lcredit, ucredit, dcredit, ocredit,is_open,edit_prof FROM password_policy where sn = ?";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 绑定参数的值
    sqlite3_bind_text(stmt, 1, g_mqtt_ob.clientInfo.device_id, -1, SQLITE_STATIC);

    // 执行查询
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        param->minlen = sqlite3_column_int(stmt, 0);
        param->minclass = sqlite3_column_int(stmt, 1);
        param->lcredit = sqlite3_column_int(stmt, 2);
        param->ucredit = sqlite3_column_int(stmt, 3);
        param->dcredit = sqlite3_column_int(stmt, 4);
        param->ocredit = sqlite3_column_int(stmt, 5);
        param->is_open = sqlite3_column_int(stmt, 6);
        param->edit_prof = sqlite3_column_int(stmt, 7);
        break;
    }

    if (rc == SQLITE_DONE)
    {
        // 查询结束
    }
    else
    {
        //dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }
    ret = 0;
error:
    if(stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

/// @brief 删除密码策略表数据
/// @param param
/// @return
bool SqlDeletePasswordPolicy()
{
    bool ret = false;

    sqlite3_stmt* stmt;
    const char* sql = "DELETE FROM password_policy";

    int rc = sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("SqlDeletePasswordPolicy SQL: %s error:%s\n", sql, sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 执行 SQL 语句
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE)
    {
        dzlog_error("SqlDeletePasswordPolicy 执行 SQL 语句出错: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 重置预处理语句以便可重复使用
    sqlite3_reset(stmt);

    ret = true;
error:
    if(stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }

    return ret;
}

/// @brief 插入2.0网络策略表
/// @param param
/// @return
int SqlINSERTIntoNetworkFirewall(RuleCollection* db_input_rules)
{
    int ret = -1;
    // 参数校验
    if (!db_input_rules || db_input_rules->count <= 0)
    {
        return ret;
    }
    sqlite3_stmt* stmt = NULL;
    const char* sql = "INSERT INTO network_firewall (policyId,chainDirection,permissionStatus,protocol,srcIp,srcPort,dstIp,dstPort,state,ruleOrigin, prio) "
        "VALUES (?,?,?,?,?,?,?,?,?,?,?) "
        "ON CONFLICT(policyId) DO UPDATE SET "
        "chainDirection = excluded.chainDirection,   "
        "permissionStatus = excluded.permissionStatus,   "
        "protocol = excluded.protocol,   "
        "srcIp = excluded.srcIp,   "
        "srcPort = excluded.srcPort,   "
        "dstIp = excluded.dstIp,   "
        "dstPort = excluded.dstPort,   "
        "state = excluded.state,   "
        "ruleOrigin = excluded.ruleOrigin,   "
        "prio = excluded.prio;"; // 冲突时更新
    // 开始事务
    if (sqlite3_exec(g_sqlite3_db, "BEGIN IMMEDIATE", NULL, NULL, NULL) != SQLITE_OK)
    {
        fprintf(stderr, "开启事务失败: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }
    // 准备预处理语句
    if (sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL) != SQLITE_OK)
    {
        fprintf(stderr, "准备语句失败: %s\n", sqlite3_errmsg(g_sqlite3_db));
        sqlite3_exec(g_sqlite3_db, "ROLLBACK", NULL, NULL, NULL);
        dzlog_error("准备语句失败: %s", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }
    // 批量操作
    int success_count = 0;
    int i = 0;
    for (i = 0; i < db_input_rules->count; i++, db_input_rules->rules++)
    {
        // 绑定参数
        sqlite3_bind_text(stmt, 1, db_input_rules->rules->policyId, -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 2, db_input_rules->rules->chainDirection, -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 3, db_input_rules->rules->permissionStatus, -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 4, db_input_rules->rules->protocol, -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 5, db_input_rules->rules->srcIp, -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 6, db_input_rules->rules->srcPort, -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 7, db_input_rules->rules->dstIp, -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 8, db_input_rules->rules->dstPort, -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 9, db_input_rules->rules->state, -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(stmt, 10, db_input_rules->rules->ruleOrigin);
        sqlite3_bind_int(stmt, 11, db_input_rules->rules->prio);
        // 执行操作
        int rc = sqlite3_step(stmt);
        if (rc != SQLITE_DONE)
        {
            fprintf(stderr, "操作失败[%d]: %s\n", i, sqlite3_errmsg(g_sqlite3_db));
            continue; // 记录失败但继续执行
        }
        success_count++;
        sqlite3_reset(stmt);
    }
    if (success_count > 0)
    {
        sqlite3_exec(g_sqlite3_db, "COMMIT", NULL, NULL, NULL);
    }
    else
    {
        sqlite3_exec(g_sqlite3_db, "ROLLBACK", NULL, NULL, NULL);
    }
    ret = success_count;
error:
    if (stmt)
    {
        // 清理资源
        sqlite3_finalize(stmt);
    }
    return ret; // 返回成功插入/更新的记录数
}

/// @brief 插入2.0网络策略表
/// @param param
/// @return
int SqlINSERTIntoNetworkFirewallOne(IOTA_NETWORKLIST_PARAM_V2* db_input_rules)
{
    int ret = -1;
    // 参数校验
    if (!db_input_rules)
    {
        goto error;
    }
    sqlite3_stmt* stmt = NULL;
    const char* sql = "INSERT INTO network_firewall (policyId,chainDirection,permissionStatus,protocol,srcIp,srcPort,dstIp,dstPort,state,ruleOrigin, prio) "
        "VALUES (?,?,?,?,?,?,?,?,?,?,?) "
        "ON CONFLICT(policyId) DO UPDATE SET "
        "chainDirection = excluded.chainDirection,   "
        "permissionStatus = excluded.permissionStatus,   "
        "protocol = excluded.protocol,   "
        "srcIp = excluded.srcIp,   "
        "srcPort = excluded.srcPort,   "
        "dstIp = excluded.dstIp,   "
        "dstPort = excluded.dstPort,   "
        "state = excluded.state,   "
        "ruleOrigin = excluded.ruleOrigin,   "
        "prio = excluded.prio;"; // 冲突时更新
    // 开始事务
    if (sqlite3_exec(g_sqlite3_db, "BEGIN IMMEDIATE", NULL, NULL, NULL) != SQLITE_OK)
    {
        fprintf(stderr, "开启事务失败: %s\n", sqlite3_errmsg(g_sqlite3_db));
        dzlog_info("SqlINSERTIntoNetworkFirewallOne start  3 error[%s]",sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }
    // 准备预处理语句
    if (sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL) != SQLITE_OK)
    {
        dzlog_error("SqlINSERTIntoNetworkFirewallOne start  4 error[%s]", sqlite3_errmsg(g_sqlite3_db));
        fprintf(stderr, "准备语句失败: %s\n", sqlite3_errmsg(g_sqlite3_db));
        sqlite3_exec(g_sqlite3_db, "ROLLBACK", NULL, NULL, NULL);
        dzlog_error("准备语句失败: %s", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }
    // 批量操作
    int success_count = 0;
    // 绑定参数
    char tmp[32] = { 0 };
    sqlite3_bind_text(stmt, 1, db_input_rules->policyId, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, db_input_rules->chainDirection, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, db_input_rules->permissionStatus, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 4, db_input_rules->protocol, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 5, db_input_rules->srcIp, -1, SQLITE_TRANSIENT);
    dzlog_info("SqlINSERTIntoNetworkFirewallOne start  5 1");
    sqlite3_bind_text(stmt, 6, db_input_rules->srcPort, -1, SQLITE_TRANSIENT);
    dzlog_info("SqlINSERTIntoNetworkFirewallOne start  5 2");
    sqlite3_bind_text(stmt, 7, db_input_rules->dstIp, -1, SQLITE_TRANSIENT);
    dzlog_info("SqlINSERTIntoNetworkFirewallOne start  5 3");
    sqlite3_bind_text(stmt, 8, db_input_rules->dstPort, -1, SQLITE_TRANSIENT);
    dzlog_info("SqlINSERTIntoNetworkFirewallOne start  5 4");
    sqlite3_bind_text(stmt, 9, db_input_rules->state, -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 10, db_input_rules->ruleOrigin);
    sqlite3_bind_int(stmt, 11, db_input_rules->prio);
    dzlog_info("SqlINSERTIntoNetworkFirewallOne start  5");
    // 执行操作
    int rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE)
    {
        fprintf(stderr, "操作失败 %s\n", sqlite3_errmsg(g_sqlite3_db));
        dzlog_info("SqlINSERTIntoNetworkFirewallOne start  51 error[%s] ", sqlite3_errmsg(g_sqlite3_db));
    }
    else
    {
        success_count++;
    }
    dzlog_info("SqlINSERTIntoNetworkFirewallOne start  6");
    sqlite3_reset(stmt);

    if (success_count > 0)
    {
        sqlite3_exec(g_sqlite3_db, "COMMIT", NULL, NULL, NULL);
    }
    else
    {
        sqlite3_exec(g_sqlite3_db, "ROLLBACK", NULL, NULL, NULL);
    }
    dzlog_info("SqlINSERTIntoNetworkFirewallOne start  7");
    ret = success_count;
error:
    if (stmt)
    {
        // 清理资源
        sqlite3_finalize(stmt);
    }
    dzlog_info("SqlINSERTIntoNetworkFirewallOne start  8");
    return ret; // 返回成功插入/更新的记录数
}

int SqlDeleteNetworkFirewallById(char* id)
{
    int ret = -1;

    sqlite3_stmt* stmt = NULL;

    const char* sql = "DELETE FROM network_firewall WHERE policyId = ?";

    int rc = sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("SqlDeleteNetworkFirewallById SQL: %s error:%s\n", sql, sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 绑定参数的值
    sqlite3_bind_text(stmt, 1, id, -1, SQLITE_STATIC);

    // 执行 SQL 语句
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE)
    {
        dzlog_error("SqlDeleteNetworkFirewallById 执行 SQL 语句出错: %s\n", sqlite3_errmsg(g_sqlite3_db));
        ret = -2;
        goto error;
    }

    // 重置预处理语句以便可重复使用
    sqlite3_reset(stmt);
    ret = 0;
error:
    if (stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }


    return ret;
}

//@brief 查询新网络策略记录ALL
//@param param
//@return
int SqlSearNetworkFirewallByid(PIOTA_NETWORKLIST_PARAM_V2 param,char * id)
{
    int ret = -1;

    sqlite3_stmt* stmt = NULL;
    int count = -1;
    const char* selectQuery = "SELECT policyId,chainDirection,permissionStatus,protocol,srcIp,srcPort,dstIp,dstPort,state,ruleOrigin,prio FROM network_firewall where policyId = ?";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    //设置查询参数
    // 绑定参数的值
    sqlite3_bind_text(stmt, 1, id, -1, SQLITE_TRANSIENT);

    // 执行查询
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        const char* data0 = (const char*)sqlite3_column_text(stmt, 0);
        if (data0 != NULL && strlen(data0) != 0)
        {
            strcpy(param->policyId, data0);
            strcpy(param->chainDirection, sqlite3_column_text(stmt, 1));
            strcpy(param->permissionStatus, sqlite3_column_text(stmt, 2));
            strcpy(param->protocol, sqlite3_column_text(stmt, 3));
            param->ruleOrigin = sqlite3_column_int(stmt, 9);
            param->prio = sqlite3_column_int(stmt, 10);
            const char* data1 = (const char*)sqlite3_column_text(stmt, 4);
            if (data1 != NULL && strlen(data1) != 0)
            {
                strcpy(param->srcIp, data1);
            }

            const char* data2 = (const char*)sqlite3_column_text(stmt, 6);
            if (data2 != NULL && strlen(data2) != 0)
            {
                strcpy(param->dstIp, data2);
            }

            const char* data3 = (const char*)sqlite3_column_text(stmt, 8);
            if (data3 != NULL && strlen(data3) != 0)
            {
                strcpy(param->state, data3);
            }

            const char* data4 = (const char*)sqlite3_column_text(stmt, 5);
            if (data4 != NULL && strlen(data4) != 0)
            {
                strcpy(param->srcPort, data4);
            }

            const char* data5 = (const char*)sqlite3_column_text(stmt, 7);
            if (data5 != NULL && strlen(data5) != 0)
            {
                strcpy(param->dstPort, data5);
            }
        }

        count++;
        param++;
    }
    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
    }
    else if (rc == SQLITE_DONE)
    {
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }
    ret = count;
error:
    if (stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

/// @brief 查询 视联配置下发记录表COUNT （记录数）
/// @param param
/// @return
int SqlVLCFGPUSHLOGCOUNT()
{
    int ret = -1;
    sqlite3_stmt* stmt = NULL;
    const char* selectQuery = "SELECT COUNT(*) FROM vl_cfg_push_log;";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 执行查询
    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        ret = sqlite3_column_int(stmt, 0);
    }
    else if (rc == SQLITE_DONE)
    {
        ret = 0;
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
        ret = 0;
    }

error:
    if (stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

//@brief 查询 视联配置下发记录表ALL
//@param param
//@return
int SqlVLCFGPUSHLOGCOUNTBYALL(PIOTA_NETWORKLIST_PARAM_V2 param)
{
    int ret = -1;
//
//    sqlite3_stmt* stmt = NULL;
//    int count = -1;
//    const char* selectQuery = "SELECT ruleOrigin,prio FROM vl_cfg_push_log;";
//    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
//    if (rc != SQLITE_OK)
//    {
//        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
//        goto error;
//    }
//
//    //设置查询参数
//    // 绑定参数的值
//    sqlite3_bind_text(stmt, 1, id, -1, SQLITE_TRANSIENT);
//
//    // 执行查询
//    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
//    {
//        const char* data0 = (const char*)sqlite3_column_text(stmt, 0);
//        if (data0 != NULL && strlen(data0) != 0)
//        {
//            strcpy(param->policyId, data0);
//            strcpy(param->chainDirection, sqlite3_column_text(stmt, 1));
//            strcpy(param->permissionStatus, sqlite3_column_text(stmt, 2));
//            strcpy(param->protocol, sqlite3_column_text(stmt, 3));
//            param->ruleOrigin = sqlite3_column_int(stmt, 9);
//            param->prio = sqlite3_column_int(stmt, 10);
//            const char* data1 = (const char*)sqlite3_column_text(stmt, 4);
//            if (data1 != NULL && strlen(data1) != 0)
//            {
//                strcpy(param->srcIp, data1);
//            }
//
//            const char* data2 = (const char*)sqlite3_column_text(stmt, 6);
//            if (data2 != NULL && strlen(data2) != 0)
//            {
//                strcpy(param->dstIp, data2);
//            }
//
//            const char* data3 = (const char*)sqlite3_column_text(stmt, 8);
//            if (data3 != NULL && strlen(data3) != 0)
//            {
//                strcpy(param->state, data3);
//            }
//
//            const char* data4 = (const char*)sqlite3_column_text(stmt, 5);
//            if (data4 != NULL && strlen(data4) != 0)
//            {
//                strcpy(param->srcPort, data4);
//            }
//
//            const char* data5 = (const char*)sqlite3_column_text(stmt, 7);
//            if (data5 != NULL && strlen(data5) != 0)
//            {
//                strcpy(param->dstPort, data5);
//            }
//        }
//
//        count++;
//        param++;
//    }
//    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
//    {
//    }
//    else if (rc == SQLITE_DONE)
//    {
//    }
//    else
//    {
//        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
//        goto error;
//    }
//    ret = count;
//error:
//    if (stmt)
//    {
//        // 释放资源
//        sqlite3_finalize(stmt);
//    }
    return ret;
}

//@brief 查询 视联配置下发记录表 是否存在对应的mid
//@param param
//@return
int SqlVLCFGPUSHLOGCOUNTBYMID(char* mid)
{
    int ret = -1;

    sqlite3_stmt* stmt = NULL;
    int count = -1;
    

    const char* selectQuery = "SELECT COUNT(*) AS record_exists  FROM `vl_cfg_push_log` WHERE `mid` = ? and code = 200;";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    //设置查询参数
    // 绑定参数的值
    sqlite3_bind_text(stmt, 1, mid, -1, SQLITE_TRANSIENT);

    // 执行查询
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        count = sqlite3_column_int(stmt, 0);
    }
    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
    }
    else if (rc == SQLITE_DONE)
    {
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }
    ret = count;
error:
    if (stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}

/// @brief 视联配置下发记录表
/// @param param
/// @return  
int SqlINSERTIntoVLCFGPUSHLOG(char* mid ,char* date , int code, char * data)
{
    int ret = -1;

    sqlite3_stmt* stmt = NULL;
    const char* sql;
    int rc = -1;
    sql = "INSERT INTO vl_cfg_push_log (mid,code,date,data) VALUES (?,?,?,?)";
    rc = sqlite3_prepare_v2(g_sqlite3_db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("SqlINSERTIntoVLCFGPUSHLOG[%s] error[%s]\n", sql, sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }
    // 绑定参数的值
    sqlite3_bind_text(stmt, 1, mid, -1, SQLITE_STATIC);
    sqlite3_bind_int(stmt, 2, code);
    sqlite3_bind_text(stmt, 3, date, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 4, data, -1, SQLITE_STATIC);

    // 执行 SQL 语句
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE)
    {
        dzlog_error("SqlINSERTIntoDirectoryMonitoring error[%s]\n", sqlite3_errmsg(g_sqlite3_db));
        ret = -2;
        goto error;
    }
    // 重置预处理语句以便可重复使用
    sqlite3_reset(stmt);
    ret = 0;
error:
    if (stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }

    return ret;
}

/// @brief 删除视联配置下发记录表记录，仅保存50条
/// @param param
/// @return
int SqlDeleteVLCFGPUSHLOG()
{
    int ret = -1;
    sqlite3_stmt* stmt = NULL;
    const char* selectQuery = "DELETE FROM vl_cfg_push_log WHERE rowid NOT IN(SELECT rowid FROM vl_cfg_push_log ORDER BY date DESC LIMIT 50);";
    int rc = sqlite3_prepare_v2(g_sqlite3_db, selectQuery, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        dzlog_error("无法编译SQL语句: %s\n", sqlite3_errmsg(g_sqlite3_db));
        goto error;
    }

    // 执行查询
    if ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        ret = 0;
    }
    else if (rc == SQLITE_DONE)
    {
        ret = 0;
    }
    else
    {
        dzlog_error("无法执行查询: %s\n", sqlite3_errmsg(g_sqlite3_db));
        ret = 0;
    }

error:
    if (stmt)
    {
        // 释放资源
        sqlite3_finalize(stmt);
    }
    return ret;
}
